import Database from 'better-sqlite3';
import path from 'path';

// 连接到 SQLite 数据库
const dbPath = path.join(process.cwd(), 'local.db');
const db = new Database(dbPath);

console.log('开始生成示例订单数据...');

// 检查是否有客户数据
const customerCount = db.prepare('SELECT COUNT(*) as count FROM customers').get() as { count: number };
if (customerCount.count === 0) {
    console.log('没有客户数据，请先创建客户');
    process.exit(1);
}

// 检查是否有产品数据
const productCount = db.prepare('SELECT COUNT(*) as count FROM products').get() as { count: number };
if (productCount.count === 0) {
    console.log('没有产品数据，请先创建产品');
    process.exit(1);
}

// 获取所有客户
const customers = db.prepare('SELECT * FROM customers').all() as any[];
// 获取所有产品
const products = db.prepare('SELECT * FROM products').all() as any[];

// 删除现有订单数据
db.prepare('DELETE FROM order_items').run();
db.prepare('DELETE FROM orders').run();
console.log('已清除现有订单数据');

// 示例订单数据
const sampleOrders = [
    {
        customerId: customers[0].id,
        totalAmount: 2999,
        status: 'pending',
        items: [
            { productId: products[0].id, quantity: 2, price: 999, subtotal: 1998 },
            { productId: products[1].id, quantity: 1, price: 1001, subtotal: 1001 }
        ]
    },
    {
        customerId: customers[1].id,
        totalAmount: 1580,
        status: 'paid',
        items: [
            { productId: products[2].id, quantity: 1, price: 1580, subtotal: 1580 }
        ]
    },
    {
        customerId: customers[2].id,
        totalAmount: 4999,
        status: 'shipped',
        items: [
            { productId: products[0].id, quantity: 1, price: 999, subtotal: 999 },
            { productId: products[3].id, quantity: 2, price: 2000, subtotal: 4000 }
        ]
    },
    {
        customerId: customers[3].id,
        totalAmount: 899,
        status: 'delivered',
        items: [
            { productId: products[4].id, quantity: 1, price: 899, subtotal: 899 }
        ]
    },
    {
        customerId: customers[4].id,
        totalAmount: 2599,
        status: 'cancelled',
        items: [
            { productId: products[1].id, quantity: 1, price: 1001, subtotal: 1001 },
            { productId: products[2].id, quantity: 1, price: 1598, subtotal: 1598 }
        ]
    }
];

// 插入订单数据
const insertOrder = db.prepare('INSERT INTO orders (customer_id, total_amount, status) VALUES (?, ?, ?)');
const insertOrderItem = db.prepare('INSERT INTO order_items (order_id, product_id, quantity, price, subtotal) VALUES (?, ?, ?, ?, ?)');

for (const orderData of sampleOrders) {
    // 插入订单
    const result = insertOrder.run(orderData.customerId, orderData.totalAmount, orderData.status);
    const orderId = result.lastInsertRowid;
    
    // 插入订单项
    for (const item of orderData.items) {
        insertOrderItem.run(orderId, item.productId, item.quantity, item.price, item.subtotal);
    }
    
    console.log(`创建订单 #${orderId}，状态: ${orderData.status}，金额: ¥${orderData.totalAmount}`);
}

console.log('示例订单数据生成完成！');
console.log(`共创建了 ${sampleOrders.length} 个订单`);

db.close();